package com.yc.hwc.dao;

import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileReader;
import java.io.IOException;
import java.lang.reflect.Array;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.query.NativeQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.transaction.annotation.Transactional;
import org.yaml.snakeyaml.Yaml;

import com.yc.hwc.bean.PageImpl;

/**
 * 执行原生SQL Service
 */

public class SqlDao {
	@PersistenceContext
	EntityManager em;

	@Value("${spring.thymeleaf.prefix}")
	private String yamlPath;
	private long yamlTime;
	private File yamlFile;

	private Map<String, Object> sqlMap = new HashMap<String, Object>();

	@SuppressWarnings("unchecked")
	public List<Map<String, Object>> find(String sql, Object... params) {
		return buildQuery(sql, params).getResultList();
	}

	@SuppressWarnings("unchecked")
	public Map<String, Object> get(String sql, Object... params) {
		return (Map<String, Object>) buildQuery(sql, params).getSingleResult();
	}

	@Transactional
	public int exec(String sql, Object... params) {
		return buildQuery(sql, params).executeUpdate();
	}

	public PageImpl<Map<String, Object>> top(String sqlid, String group, String condition, 
			int tops, int page, int size, Object... params) {
		loadYaml();
		if (sqlMap.containsKey(sqlid)) {
			String sql = sqlMap.get(sqlid).toString();
			String topSql = "select * from (<sql>) a where ("
					+ "select count(*) from (<sql>) b"
					+ " where a.<group>=b.<group> and a.<condition><order>b.<condition>) < <tops>";
			topSql = topSql.replaceAll("<sql>", sql)
					.replaceAll("<group>", group)
					.replaceAll("<condition>", condition)
					.replaceAll("<order>", tops > 0 ? "<" : ">")
					.replaceAll("<tops>", (tops > 0 ? tops : -tops) + "");
			return page(topSql, page, size, params);
		} else {
			throw new RuntimeException("没有该SQL: " + sqlid);
		}
	}

	public List<Map<String, Object>> list(String sqlid, Object... params) {
		loadYaml();
		if (sqlMap.containsKey(sqlid)) {
			String sql = sqlMap.get(sqlid).toString();
			return find(sql, params);
		} else {
			throw new RuntimeException("没有该SQL: " + sqlid);
		}
	}

	public PageImpl<Map<String, Object>> page(String sql, int page, int size, Object... params) {
		PageImpl<Map<String, Object>> ret = new PageImpl<Map<String, Object>>();
		ret.setSize(size);
		ret.setNumber(page);
		String listSql = "select * from (" + sql + ") a limit " + --page * size + "," + size;
		String rowsSql = "select count(*) rows from (" + sql + ") a";
		ret.setContent(find(listSql, params));
		ret.setTotalElements(Long.parseLong("" + find(rowsSql, params).get(0).get("rows")));
		return ret;
	}

	public Map<String, Object> one(String sqlid, Object... params) {
		List<Map<String, Object>> list = list(sqlid, params);
		return list == null || list.isEmpty() ? null : list.get(0);
	}

	public Object val(String sqlid, Object... params) {
		Map<String, Object> map = one(sqlid, params);
		return map == null ? null : map.values().iterator().next();
	}

	@SuppressWarnings("deprecation")
	public Query buildQuery(String sql, Object... params) {
		System.out.println("参数: " + java.util.Arrays.toString(params));
		Query query = em.createNativeQuery(sql);
		query.unwrap(NativeQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		for (int i = 0; i < params.length; i++) {
			if (params[i] instanceof Map) {
				Map<?, ?> m = (Map<?, ?>) params[i];
				for (Entry<?, ?> e : m.entrySet()) {
					String name = e.getKey().toString();
					// 过滤出一些特殊参数，例如 order 排序
					if ("order".equalsIgnoreCase(name)) {
						continue;
					}
					Object val = e.getValue();
					String regex = "(\\w+):(\\w+)";
					if (name.matches(regex)) {
						String type = name.replaceAll(regex, "$2");
						name = name.replaceAll(regex, "$1");
						val = cast(type, val);
					}
					query.setParameter(name, val);
				}
			} else {
				System.out.println(i + ":" + params[i]);
				query.setParameter(i, params[i]);
			}
		}

		return query;
	}

	private Object cast(String type, Object value) {
		if ("int".equals(type)) {
			if (value == null) {
				return null;
			} else if (value.getClass().isArray()) {
				Integer[] ret = new Integer[Array.getLength(value)];
				for (int i = 0; i < ret.length; i++) {
					try {
						ret[i] = Integer.valueOf("" + Array.get(value, i));
					} catch (Exception e) {
						continue;
					}
				}
			} else {
				try {
					return Integer.valueOf("" + value);
				} catch (Exception e) {
					return null;
				}
			}
		}
		return value;
	}

	@SuppressWarnings("unchecked")
	private void loadYaml() {
		if (yamlFile == null) {
			String path = yamlPath + "db/sql.yml";
			path = path.substring("file:".length());
			yamlFile = new File(path);
		}

		if (yamlFile.lastModified() != yamlTime) {
			yamlTime = yamlFile.lastModified();
			FileReader fr = null;
			try {
				fr = new FileReader(yamlFile);
				for (Object o : new Yaml().loadAll(fr)) {
					Map<String, Object> map = (Map<String, Object>) o;
					sqlMap.putAll(map);
				}
			} catch (FileNotFoundException e) {
				throw new RuntimeException(e);
			} finally {
				if (fr != null)
					try {
						fr.close();
					} catch (IOException e) {
						throw new RuntimeException(e);
					}
			}
		}
	}

}